package com.bootdo.common.utils.excel;

import cn.hutool.core.map.MapUtil;
import com.bootdo.common.utils.StringUtils;
import com.bootdo.wx.dao.MemberUserDao;
import com.bootdo.wx.domain.MemberUserDO;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;

import javax.annotation.PostConstruct;
import javax.servlet.ServletOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by HP on 2019/4/12.
 */
public class OrderExcelUtils {

    @Autowired
    private MemberUserDao memberUserDao;



    public static OrderExcelUtils orderExcelUtils;

    @PostConstruct
    public void init(){
        orderExcelUtils = this;
        orderExcelUtils.memberUserDao = this.memberUserDao;
    }




    /**
     * 将用户的信息导入到excel文件中去
     * @param orderList 代发货订单列表
     * @param out 输出表
     */
    public static int exportUserExcel(List<Map<String,Object>> orderList,ServletOutputStream out)
    {
        try{
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格对象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,10);//起始行,结束行,起始列,结束列
            //1.2头标题样式
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
            //1.3列标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
            //1.4 内容样式
            /*HSSFCellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setWrapText(true); //自动换行 */
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("待发货订单列表");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(20);
            //设置列宽
            sheet.setColumnWidth(3, 10000);
            sheet.setColumnWidth(4, 10000);
            sheet.setColumnWidth(7, 9000);
            sheet.setColumnWidth(8, 10000);
            /*sheet.setColumnWidth(0, 17);
            sheet.setColumnWidth(1, 10);
            sheet.setColumnWidth(2, 20);
            sheet.setColumnWidth(3, 23);
            sheet.setColumnWidth(4, 20);
            sheet.setColumnWidth(5, 5);
            sheet.setColumnWidth(6, 7);
            sheet.setColumnWidth(7, 17);
            sheet.setColumnWidth(8, 25);
            sheet.setColumnWidth(9, 15);
            sheet.setColumnWidth(10, 20);*/


            //3.创建行
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);

            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("订单列表");

            //3.2创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            //String[] titles = {"订单号","待发货","金额","商品名","货品规格","SKU","数量","收件人姓名","收件人电话","收件人地址","买家留言"};
            String[] titles = {"订单号","收件人姓名","收件人电话","收件人地址","商品名","货品规格","数量","买家留言","订单状态","金额","sku","下单人","第一审单人","第二审单人","第三审单人","订单提交人账号","订单提交人昵称","订单提交时间","订单创建时间"};
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            //4.操作单元格;将用户列表写入excel
            if(orderList != null)
            {
                int size = 1;
                int j = 0;
                for(Map<String,Object> map : orderList) {
                    List<Map<String,Object>> goodsList = (List<Map<String,Object>>)(map.get("goodsList"));
                    size = goodsList.size();
                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j+2);

                    HSSFCell cell0 = row3.createCell(0);
                    cell0.setCellValue(StringUtils.getString(map.get("order_sn")));
                    HSSFCell cell1 = row3.createCell(1);
                    cell1.setCellValue(StringUtils.getString(map.get("receiver_name")));
                    HSSFCell cell2 = row3.createCell(2);
                    cell2.setCellValue(StringUtils.getString(map.get("receiver_phone")));
                    String address = StringUtils.getString(map.get("receiver_province")) + StringUtils.getString(map.get("receiver_city"))
                            + StringUtils.getString(map.get("receiver_county")) + StringUtils.getString(map.get("receiver_address"));
                    HSSFCell cell3 = row3.createCell(3);
                    cell3.setCellValue(address);
                    List<Map<String,Object>> detailList = (List<Map<String,Object>>) map.get("goodsList");
                    HSSFCell cell4 = row3.createCell(4);
                    cell4.setCellValue(StringUtils.getString(detailList.get(0).get("name").toString()));
                    HSSFCell cell5 = row3.createCell(5);
                    cell5.setCellValue(StringUtils.getString(detailList.get(0).get("spec")));
                    HSSFCell cell6 = row3.createCell(6);
                    cell6.setCellValue(StringUtils.getString(detailList.get(0).get("total")));

                    HSSFCell cell7 = row3.createCell(7);
                    cell7.setCellValue(StringUtils.getString(map.get("buyer_msg")));
                    HSSFCell cell8 = row3.createCell(8);
                    int status = Integer.valueOf(map.get("status").toString());
                    if (status == 0) {
                        cell8.setCellValue("砍价中");
                    } else if (status == 1) {
                        cell8.setCellValue("待支付");
                    } else if (status == 3) {
                        cell8.setCellValue("待发货");
                    } else if (status == 4) {
                        cell8.setCellValue("待收货");
                    } else if (status == 5) {
                        cell8.setCellValue("退货中");
                    } else if (status == 6) {
                        cell8.setCellValue("退货完成");
                    } else if (status == 7) {
                        cell8.setCellValue("已完成");
                    } else if (status == 8) {
                        cell8.setCellValue("订单取消");
                    } else if (status == 9) {
                        cell8.setCellValue("砍价失败");
                    }
                    HSSFCell cell9 = row3.createCell(9);
                    cell9.setCellValue(StringUtils.getString(map.get("payment")));
                    HSSFCell cell10 = row3.createCell(10);
                    cell10.setCellValue(StringUtils.getString(detailList.get(0).get("sku").toString()));
                    HSSFCell cell11 = row3.createCell(11);
                    cell11.setCellValue(MapUtil.getStr(map,"phone"));
                    /*HSSFCell cell12 = row3.createCell(12);
                    cell12.setCellValue(StringUtils.getString(map.get("userName").toString()));*/

                    /*第一收单人*/
                    HashMap<String, Object> objectObjectHashMap = MapUtil.newHashMap();

                    objectObjectHashMap.put("userId",MapUtil.getStr(map,"userId"));
                    objectObjectHashMap.put("level","0");
                    objectObjectHashMap.put("groupby","1");
                    HSSFCell cell12 = row3.createCell(12);
                    HSSFCell cell13 = row3.createCell(13);
                    HSSFCell cell14 = row3.createCell(14);
                    HSSFCell cell15 = row3.createCell(15);
                    HSSFCell cell16 = row3.createCell(16);
                    List<MemberUserDO> upUserList = (List<MemberUserDO>) map.get("upUserList");
                   // List<MemberUserDO> myUpVip = orderExcelUtils.memberUserDao.getMyUpVip(objectObjectHashMap);
                    if(upUserList.size()>0){
                        for (MemberUserDO userDO : upUserList) {
                            if(userDO.getLevel()==1){
                                cell12.setCellValue(StringUtils.getString(userDO.getPhone()));
                            }else if(userDO.getLevel()==2){
                                cell13.setCellValue(StringUtils.getString(userDO.getPhone()));
                            }else if(userDO.getLevel()==3){
                                cell14.setCellValue(StringUtils.getString(userDO.getPhone()));
                            }
                        }
                        cell15.setCellValue(StringUtils.getString( upUserList.get(upUserList.size()-1).getPhone()));
                        cell16.setCellValue(StringUtils.getString( upUserList.get(upUserList.size()-1).getName()));

                    }else{
                        cell15.setCellValue(StringUtils.getString(map.get("phone")));
                        cell16.setCellValue(StringUtils.getString(map.get("create_at").toString()));
                    }


                    //MemberUserDO memberUserDO = orderExcelUtils.memberUserDao.get(MapUtil.getStr(map, "userId"));
                   /* HSSFCell cell16 = row3.createCell(16);
                    cell16.setCellValue(StringUtils.getString(map.get("phone")));*/
                   /* HSSFCell cell17 = row3.createCell(17);
                    cell17.setCellValue(StringUtils.getString(map.get("userName")));*/
                    HSSFCell cell17 = row3.createCell(17);
                    cell17.setCellValue(StringUtils.getString(map.get("create_at").toString()));
                    HSSFCell cell18 = row3.createCell(18);
                    cell18.setCellValue(StringUtils.getString(map.get("create_at").toString()));
                    /*if(myUpVip.size()>0){

                        cell13.setCellValue(StringUtils.getString(myUpVip.get(0).getPhone()));
                    }else{
                        cell13.setCellValue("");
                    }*/
                   /* HSSFCell cell13 = row3.createCell(13);
                    cell13.setCellValue(StringUtils.getString(map.get("create_at").toString()));*/
                    if (size > 1) {
                        //合并单元格
                        CellRangeAddress callRangeAddress0 = new CellRangeAddress(j+2,j+1+size,0,0);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress0);
                        CellRangeAddress callRangeAddress1 = new CellRangeAddress(j+2,j+1+size,1,1);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress1);
                        CellRangeAddress callRangeAddress2 = new CellRangeAddress(j+2,j+1+size,2,2);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress2);
                        CellRangeAddress callRangeAddress3 = new CellRangeAddress(j+2,j+1+size,3,3);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress3);
                        CellRangeAddress callRangeAddress4 = new CellRangeAddress(j+2,j+1+size,4,4);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress4);
                        CellRangeAddress callRangeAddress5 = new CellRangeAddress(j+2,j+1+size,5,5);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress5);
                        CellRangeAddress callRangeAddress6 = new CellRangeAddress(j+2,j+1+size,6,6);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress6);
                        CellRangeAddress callRangeAddress7 = new CellRangeAddress(j+2,j+1+size,7,7);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress7);
                        CellRangeAddress callRangeAddress8 = new CellRangeAddress(j+2,j+1+size,8,8);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress8);
                        CellRangeAddress callRangeAddress9 = new CellRangeAddress(j+2,j+1+size,9,9);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress9);
                        CellRangeAddress callRangeAddress10 = new CellRangeAddress(j+2,j+1+size,10,10);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress10);
                        CellRangeAddress callRangeAddress11 = new CellRangeAddress(j+2,j+1+size,11,11);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress11);
                        CellRangeAddress callRangeAddress12 = new CellRangeAddress(j+2,j+1+size,12,12);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress12);
                        CellRangeAddress callRangeAddress13 = new CellRangeAddress(j+2,j+1+size,13,13);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress13);
                        CellRangeAddress callRangeAddress14 = new CellRangeAddress(j+2,j+1+size,14,14);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress14);
                        CellRangeAddress callRangeAddress15 = new CellRangeAddress(j+2,j+1+size,15,15);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress15);
                        CellRangeAddress callRangeAddress16 = new CellRangeAddress(j+2,j+1+size,16,16);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress16);
                        CellRangeAddress callRangeAddress17 = new CellRangeAddress(j+2,j+1+size,17,17);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress17);
                        CellRangeAddress callRangeAddress18 = new CellRangeAddress(j+2,j+1+size,18,18);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress18);
                        /*CellRangeAddress callRangeAddress19 = new CellRangeAddress(j+2,j+1+size,19,19);//起始行,结束行,起始列,结束列
                        sheet.addMergedRegion(callRangeAddress19);*/
                    }
                    /*for (int y=0; y<goodsList.size(); y++) {
                        Map<String,Object> goodsMap = goodsList.get(y);
                        HSSFRow rows;
                        if (y == 0) {
                            rows = row3;
                        } else {
                            rows = sheet.createRow(j + y+2);
                        }
                        HSSFCell cell7 = rows.createCell(7);
                        cell7.setCellValue(StringUtils.getString(goodsMap.get("name")));
                        HSSFCell cell8 = rows.createCell(8);
                        cell8.setCellValue(StringUtils.getString(goodsMap.get("spec")));
                        HSSFCell cell9 = rows.createCell(9);
                        cell9.setCellValue(StringUtils.getString(goodsMap.get("total")));
                        HSSFCell cell10 = rows.createCell(10);
                        cell10.setCellValue(StringUtils.getString(goodsMap.get("price")));
                    }*/
                    j = j+size;
                }
            }
            //5.输出
            workbook.write(out);
            workbook.close();
            return 1;
            //out.close();
        }catch(Exception e)
        {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     *
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }




}
